﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook.Sales
{
    public class CreditMemoManager:ConnectionManager
    {
        public SqlException AddCreditMemo(CreditMemoUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "INSERT INTO OCM (DocNum, CardCode, CardName, Remarks, TotalAmount, DocDate, DateCreated, CreatedBy, DateModified, ModifiedBy, DocStatus) VALUES (@DocNum, @CardCode, @CardName, @Remarks, @TotalAmount, @DocDate, @DateCreated, @CreatedBy, @DateModified, @ModifiedBy, @DocStatus)";

                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocNum));
                oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
                oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
                oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
                oCommand.Parameters.Add(new SqlParameter("@TotalAmount", Convert.ToDouble(oUnit.TotalAmount)));
                oCommand.Parameters.Add(new SqlParameter("@DocDate", oUnit.DocDate));
                oCommand.Parameters.Add(new SqlParameter("@DateCreated", oUnit.DateCreated));
                oCommand.Parameters.Add(new SqlParameter("@CreatedBy", oUnit.CreatedBy));
                oCommand.Parameters.Add(new SqlParameter("@DateModified", oUnit.DateModified));
                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));
                oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocStatus));

                oCommand.ExecuteNonQuery();

                oCommand = new SqlCommand();

                foreach (DataRow oRow in oUnit.LineItems.Rows)
                {
                    oCommand = new SqlCommand();

                    oCommand.Connection = this.Connection;
                    oCommand.CommandText = "INSERT INTO CM1 (DocNum, AcctCode, AcctName, Particulars, Amount) VALUES (@DocNum, @AcctCode, @AcctName, @Particulars, @Amount) ";
                    oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocNum));
                    oCommand.Parameters.Add(new SqlParameter("@AcctCode", oRow["AcctCode"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@AcctName", oRow["AcctName"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@Particulars", oRow["Particulars"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@Amount", oRow["Amount"].ToString()));

                    oCommand.ExecuteNonQuery();
                }

                return null;

            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }

        }

        public SqlException UpdateCreditMemo(CreditMemoUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE OCM SET CardCode=@CardCode, CardName=@CardName, Remarks=@Remarks, TotalAmount=@TotalAmount, DocDate=@DocDate, DateModified=@DateModified, ModifiedBy=@ModifiedBy, DocStatus=@DocStatus WHERE DocNum=@DocNum";

                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocNum));
                oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
                oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
                oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
                oCommand.Parameters.Add(new SqlParameter("@TotalAmount", oUnit.TotalAmount));
                oCommand.Parameters.Add(new SqlParameter("@DocDate", oUnit.DocDate));
                oCommand.Parameters.Add(new SqlParameter("@DateModified", oUnit.DateModified));
                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));
                oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocStatus));

                oCommand.ExecuteNonQuery();

                oCommand = new SqlCommand();
                oCommand.CommandType = CommandType.Text;
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "DELETE CM1 WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocNum));
                oCommand.ExecuteNonQuery();
                foreach (DataRow oRow in oUnit.LineItems.Rows)
                {
                    if (oRow.RowState != DataRowState.Deleted)
                    {
                        oCommand = new SqlCommand();

                        oCommand.Connection = this.Connection;
                        oCommand.CommandText = "INSERT INTO CM1 (DocNum, AcctCode, AcctName, Particulars, Amount) VALUES (@DocNum, @AcctCode, @AcctName, @Particulars, @Amount) ";
                        oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocNum));
                        oCommand.Parameters.Add(new SqlParameter("@AcctCode", oRow["AcctCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@AcctName", oRow["AcctName"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Particulars", oRow["Particulars"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Amount", oRow["Amount"].ToString()));

                        oCommand.ExecuteNonQuery();
                    }
                }

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public SqlException SetAppliedAmount(string DocumentNo, string _AMOUNT)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE OCM SET AppliedAmount=AppliedAmount+@AppliedAmount WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", DocumentNo));
                oCommand.Parameters.Add(new SqlParameter("@AppliedAmount", _AMOUNT));

                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public DataTable GetCreditMemo(string sDocNum)
        {
            SqlCommand oCommand = new SqlCommand();
            DataTable dt = new DataTable();
            
            oCommand.Connection = this.Connection;

            oCommand.CommandText = "SELECT * FROM OCM WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocNum));

            SqlDataAdapter adptr = new SqlDataAdapter();

            adptr.SelectCommand = oCommand;
            adptr.Fill(dt);

            return dt;
        }

        public DataTable GetCreditMemoLineItems(string sDocNum)
        {
            DataTable dt = new DataTable();

            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;

            oCommand.CommandText = "SELECT * FROM CM1 WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocNum));

            SqlDataAdapter adptr = new SqlDataAdapter();

            adptr.SelectCommand = oCommand;
            adptr.Fill(dt);

            return dt;
        }

        public DataTable BrowseCreditMemoByDocNum(string sDocNum)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            sDocNum = "%" + sDocNum + "%";

            oCommand.Connection = this.Connection;

            oCommand.CommandText = "SELECT * FROM OCM WHERE DocNum LIKE @DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocNum));

            SqlDataAdapter adptr = new SqlDataAdapter();

            adptr.SelectCommand = oCommand;
            adptr.Fill(dt);

            return dt;
        }

        public DataTable BrowseCreditMemoByDocNum(string CardCode, string sDocNum)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            sDocNum = "%" + sDocNum + "%";

            oCommand.Connection = this.Connection;

            oCommand.CommandText = "SELECT *, TotalAmount - AppliedAmount TotalBalance FROM OCM WHERE CardCode=@CardCode AND DocNum LIKE @DocNum  and DocStatus='Posted'";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocNum));
            oCommand.Parameters.Add(new SqlParameter("@CardCode", CardCode));

            SqlDataAdapter adptr = new SqlDataAdapter();

            adptr.SelectCommand = oCommand;
            adptr.Fill(dt);

            return dt;
        }

        public DataTable BrowseCreditMemoByRemarks(string sRemarks)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            sRemarks = "%" + sRemarks + "%";

            oCommand.Connection = this.Connection;

            oCommand.CommandText = "SELECT * FROM OCM WHERE Remarks LIKE @Remarks";
            oCommand.Parameters.Add(new SqlParameter("@Remarks", sRemarks));

            SqlDataAdapter adptr = new SqlDataAdapter();

            adptr.SelectCommand = oCommand;
            adptr.Fill(dt);

            return dt;
        }

        public DataTable BrowseCreditMemoByRemarks(string CardCode, string sRemarks)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            sRemarks = "%" + sRemarks + "%";

            oCommand.Connection = this.Connection;

            oCommand.CommandText = "SELECT *, TotalAmount - AppliedAmount TotalBalance FROM OCM WHERE CardCode=@CardCode AND Remarks LIKE @Remarks and DocStatus='Posted'";
            oCommand.Parameters.Add(new SqlParameter("@Remarks", sRemarks));
            oCommand.Parameters.Add(new SqlParameter("@CardCode", CardCode));

            SqlDataAdapter adptr = new SqlDataAdapter();

            adptr.SelectCommand = oCommand;
            adptr.Fill(dt);

            return dt;
        }


        public void CloseDocument(string sDocNum)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;

            oCommand.CommandText = "UPDATE OCM SET DOCSTATUS='Closed' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocNum));

            oCommand.ExecuteNonQuery();
        }


        public void CancelDocument(string sDocNum)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;

            oCommand.CommandText = "UPDATE OCM SET DOCSTATUS='Canceled' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocNum));
            oCommand.ExecuteNonQuery();
        }


        public void PostDocument(string sDocNum)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;

            oCommand.CommandText = "UPDATE OCM SET DOCSTATUS='Posted' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocNum));
            oCommand.ExecuteNonQuery();
        }
    }
}
